package org.jeecg.modules.xxgx.common.util;


import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;

//Excel导入导出工具类
public class ExcelUtils {
    protected static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                                   boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     *
     * @param list 数据列表
     * @param title 标题
     * @param sheetName sheet名字
     * @param pojoClass 导出对象的Class类型
     * @param fileName 文件名
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                                   HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
                                      ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null)
            ;
        downLoadExcel(fileName, response, workbook);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            // throw new NormalException(e.getMessage());
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null)
            ;
        downLoadExcel(fileName, response, workbook);
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            // throw new NormalException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            // throw new NormalException(e.getMessage());
        }
        return list;
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,
                                          Class<T> pojoClass) {
        ImportParams params = new ImportParams();
        return importExcel(file,titleRows,headerRows,pojoClass,params);
    }

    public static <T> List<T> importExcel(MultipartFile file,
                                          Integer titleRows, Integer headerRows,
                                          Class<T> pojoClass,ImportParams params) {
        if (file == null) {
            return null;
        }
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            // throw new NormalException("excel文件不能为空");
        } catch (Exception e) {
            // throw new NormalException(e.getMessage());
            System.out.println(e.getMessage());
        }
        return list;
    }

    /**
     * 读取excel数据测试，支持xls和xlsx返回List
     *
     * @param file
     * @param startIndex 开始读取的行索引
     * @return
     */
    public static List<Object> getAllExcelRow(File file, int sheetIndex, int startIndex) throws IOException {
        ArrayList<Object> lRowObj = new ArrayList<>();
        Workbook workbook = null;

        try {
            workbook = new HSSFWorkbook(new FileInputStream(file));
        } catch (Exception ex) {
            workbook = new XSSFWorkbook(new FileInputStream(file));
        }

        try {
            int index = sheetIndex < 0 ? 0 : sheetIndex;
            startIndex = startIndex < 0 ? 1 : startIndex;
            Sheet sheet = null;
            Row row = null;
            Cell cell = null;
            // 取得第index个Sheet
            sheet = workbook.getSheetAt(index);
            int rowNum = sheet.getLastRowNum();
            row = sheet.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();

            // 正文内容应该从开始行索引开始
            for (int i = startIndex - 1; i <= rowNum; i++) {
                row = sheet.getRow(i);
                if (row != null) {//对于获取的row可能为null的情况进行处理，防止出现空指针异常
                    int j = 0;
                    int m = 0;
                    ArrayList<String> lCellStr = new ArrayList<>();
                    while (j < colNum) {
                        cell = row.getCell(j);
                        if (cell != null) {
                            String value;
                            if (cell.getCellType().equals(CellType.NUMERIC)) {
                                value = new BigDecimal(String.valueOf(cell.getNumericCellValue())).stripTrailingZeros().toPlainString();
                            } else {
                                cell.setCellType(CellType.STRING);//br-Excel导入Cannot get a text value from a numeric cell
                                value = cell.getStringCellValue();
                            }
                            lCellStr.add(j, value);
                        } else {
                            lCellStr.add(j, "");
                        }
                        j++;

                    }
                    for (int k = 0; k < lCellStr.size(); k++) {
                        if ("".equals(lCellStr.get(k))) {
                            m++;
                        }
                    }
                    if (m < colNum) {
                        lRowObj.add(lCellStr);
                    }
                }
            }
        } catch (Exception e) {
            logger.error("excel读取失败{},{}", e.getMessage(), e);
        } finally {
            if (workbook != null) {
                try{
                    workbook.close();
                }catch(Exception e){
                    logger.error("workbook关闭失败{},{}", e.getMessage(), e);
                }
            }
        }
        return lRowObj;
    }

    public static void replaceColumnValue(Sheet sheet, int columnIndex, int startRow, String oldValue, String newValue) {
        DataFormatter dataFormatter = new DataFormatter();
        Iterator<Row> rowIterator = sheet.rowIterator();
        while (rowIterator.hasNext()){
            Row row = rowIterator.next();
            if(row.getRowNum()<startRow){
                continue;
            }
            Cell cell = row.getCell(columnIndex);
            String cellValue = dataFormatter.formatCellValue(cell);
            if(StringUtils.equals(cellValue,oldValue)){
                cell.setCellValue(newValue);
            }
        }
    }





    /**
     * 特定行业名称转代码
     * @param tdhylx
     * @return
     */
    public static String tdhy_sjfz(String tdhylx) {
        String tdhylxdm="";
        if("正常开具".equals(tdhylx)){
            tdhylxdm="00";
        }else if("成品油".equals(tdhylx)){
            tdhylxdm="01";
        }else if("稀土".equals(tdhylx)){
            tdhylxdm="02";
        }else if("建筑服务".equals(tdhylx)){
            tdhylxdm="03";
        }else if("货物运输服务".equals(tdhylx)){
            tdhylxdm="04";
        }else if("不动产销售".equals(tdhylx)){
            tdhylxdm="05";
        }else if("不动产经营租赁服务".equals(tdhylx)){
            tdhylxdm="06";
        }else if("代收车船税".equals(tdhylx)){
            tdhylxdm="07";
        }else if("通行费".equals(tdhylx)){
            tdhylxdm="08";
        }else if("旅客运输服务".equals(tdhylx)){
            tdhylxdm="09";
        }else if("医疗服务（门诊）".equals(tdhylx)){
            tdhylxdm="11";
        }else if("医疗服务（住院）".equals(tdhylx)){
            tdhylxdm="10";
        }else if("自产农产品销售".equals(tdhylx)){
            tdhylxdm="12";
        }else if("拖拉机和联合收割机".equals(tdhylx)){
            tdhylxdm="13";
        }else if("机动车".equals(tdhylx)){
            tdhylxdm="14";
        }else if("二手车".equals(tdhylx)){
            tdhylxdm="15";
        }else if("农产品收购".equals(tdhylx)){
            tdhylxdm="16";
        }else if("光伏收购".equals(tdhylx)){
            tdhylxdm="17";
        }else if("卷烟".equals(tdhylx)){
            tdhylxdm="18";
        }else if("出口".equals(tdhylx)){
            tdhylxdm="19";
        }else if("农产品".equals(tdhylx)){
            tdhylxdm="20";
        }else{
            tdhylxdm="";
        }
        return tdhylxdm;
    }


    /**
     * 特定行业类型  数据封装
     * @return
     */
    public static JSONObject tdhy_sjfz(List<Map<Integer, String>> tdhyList,String tdhylx,String fplsh) {
        JSONObject tdhyjson=new JSONObject();
        if(tdhyList !=null){
            for (Map<Integer, String> sjMap : tdhyList) {
                if(fplsh.equals(sjMap.get(0))){
                    if("建筑服务".equals(tdhylx)){
                        //建筑服务发生地	发生地详细地址	建筑项目名称	土地增值税项目编号	跨地市标志
                        tdhyjson.put("jzfwfsd",sjMap.get(1));
                        tdhyjson.put("fsdxxdz",sjMap.get(2));
                        tdhyjson.put("jzxmmc",sjMap.get(3));
                        tdhyjson.put("tdzzsxmbh",sjMap.get(4));
                        tdhyjson.put("kdsbz",sjMap.get(5));
                        tdhyjson.put("tdhylxdm","03");
                    }else if("货物运输服务".equals(tdhylx)){
                        //起运地  到达地	运输工具种类	运输工具牌号	运输货物名称
                        tdhyjson.put("qyd",sjMap.get(6));
                        tdhyjson.put("ddd",sjMap.get(7));
                        tdhyjson.put("ysgjzl",sjMap.get(8));
                        tdhyjson.put("ysgzph",sjMap.get(9));
                        tdhyjson.put("yshwmc",sjMap.get(10));
                        tdhyjson.put("tdhylxdm","04");
                    }else if("不动产销售".equals(tdhylx)){
                        //房屋产权证书/不动产权证号码	不动产地址  不动产详细地址	不动产单元代码/网签合同备案编码	土地增值税项目编号	核定计税价格	实际成交含税金额  跨地市标志	面积单位
                        tdhyjson.put("fwcqzs",sjMap.get(11));
                        tdhyjson.put("bdcdz",sjMap.get(12));
                        tdhyjson.put("bdcxxdz",sjMap.get(13));
                        tdhyjson.put("bdcdydm",sjMap.get(14));
                        tdhyjson.put("tdzzsxmbh",sjMap.get(15));
                        tdhyjson.put("hdjsjg",sjMap.get(16));
                        tdhyjson.put("sjcjhsje",sjMap.get(17));
                        tdhyjson.put("kdsbz",sjMap.get(18));
                        tdhyjson.put("mjdw",sjMap.get(19));
                        tdhyjson.put("tdhylxdm","05");
                    }else if("不动产经营租赁服务".equals(tdhylx)){
                        // 房屋产权证书/不动产权证号码	不动产地址	不动产详细地址	租赁期起	租赁期止  跨地市标志	面积单位
                        tdhyjson.put("fwcqzs",sjMap.get(20));
                        tdhyjson.put("bdcdz",sjMap.get(21));
                        tdhyjson.put("bdcxxdz",sjMap.get(22));
                        tdhyjson.put("zlqq",sjMap.get(23));
                        tdhyjson.put("zlqz",sjMap.get(24));
                        tdhyjson.put("kdsbz",sjMap.get(25));
                        tdhyjson.put("mjdw",sjMap.get(26));
                        tdhyjson.put("tdhylxdm","06");
                    }else if("代收车船税".equals(tdhylx)){
                        // 保险单号	车牌号/船舶登记号	税款所属期起	税款所属期止	代收车船税金额  滞纳金金额	金额合计	车辆识别代码/车架号码
                        tdhyjson.put("bxdh",sjMap.get(27));
                        tdhyjson.put("cphcbdjh",sjMap.get(28));
                        tdhyjson.put("skssqq",sjMap.get(29));
                        tdhyjson.put("skssqz",sjMap.get(30));
                        tdhyjson.put("dsccsje",sjMap.get(31));
                        tdhyjson.put("znjje",sjMap.get(32));
                        tdhyjson.put("jehj",sjMap.get(33));
                        tdhyjson.put("clsbdm",sjMap.get(34));
                        tdhyjson.put("tdhylxdm","07");
                    }else if("旅客运输服务".equals(tdhylx)){
                        // 	出行人	出行人证件类型	出行人证件号码	出行日期   出发地	到达地	交通工具类型	等级
                        tdhyjson.put("cxr",sjMap.get(35));
                        tdhyjson.put("cxrzjlx",sjMap.get(36));
                        tdhyjson.put("cxrzjhm",sjMap.get(37));
                        tdhyjson.put("cxrq",sjMap.get(38));
                        tdhyjson.put("cfd",sjMap.get(39));
                        tdhyjson.put("ddd",sjMap.get(40));
                        tdhyjson.put("jtgjlx",sjMap.get(41));
                        tdhyjson.put("dj",sjMap.get(42));
                        tdhyjson.put("tdhylxdm","09");
                    }else if("医疗服务（门诊）".equals(tdhylx)){
                        // 	业务流水号	门诊号	就诊日期	医疗机构类型	医保类型  医保编号	性别	医保统筹基金支付  其他支付	个人账户支付	个人现金支付	个人支付	个人自费
                        tdhyjson.put("ywlsh",sjMap.get(43));
                        tdhyjson.put("mzh",sjMap.get(44));
                        tdhyjson.put("jzrq",sjMap.get(45));
                        tdhyjson.put("yljglx",sjMap.get(46));
                        tdhyjson.put("yblx",sjMap.get(47));
                        tdhyjson.put("ybbh",sjMap.get(48));
                        tdhyjson.put("xb",sjMap.get(49));
                        tdhyjson.put("ybtcjjzf",sjMap.get(50));
                        tdhyjson.put("qtzf",sjMap.get(51));
                        tdhyjson.put("grzhzf",sjMap.get(52));
                        tdhyjson.put("grxjzf",sjMap.get(53));
                        tdhyjson.put("grzfu",sjMap.get(54));
                        tdhyjson.put("grzfei",sjMap.get(55));
                        tdhyjson.put("tdhylxdm","11");
                    }else if("医疗服务（住院）".equals(tdhylx)){
                        // 	业务流水号	病例号	住院号  住院科别	住院时间	预缴金额	补缴金额	退费金额	医疗机构类型	医保类型	医保编号	性别
                        // 	医保统筹基金支付	其他支付	个人账户支付	个人现金支付	个人支付	个人自费
                        tdhyjson.put("ywlsh",sjMap.get(56));
                        tdhyjson.put("blh",sjMap.get(57));
                        tdhyjson.put("zyh",sjMap.get(58));
                        tdhyjson.put("zykb",sjMap.get(59));
                        tdhyjson.put("zysj",sjMap.get(60));
                        tdhyjson.put("yjje",sjMap.get(61));
                        tdhyjson.put("bjje",sjMap.get(62));
                        tdhyjson.put("tfje",sjMap.get(63));
                        tdhyjson.put("yljglx",sjMap.get(64));
                        tdhyjson.put("yblx",sjMap.get(65));
                        tdhyjson.put("ybbh",sjMap.get(66));
                        tdhyjson.put("xb",sjMap.get(67));
                        tdhyjson.put("ybtcjjzf",sjMap.get(68));
                        tdhyjson.put("qtzf",sjMap.get(69));
                        tdhyjson.put("grzhzf",sjMap.get(70));
                        tdhyjson.put("grxjzf",sjMap.get(71));
                        tdhyjson.put("grzfu",sjMap.get(72));
                        tdhyjson.put("grzfei",sjMap.get(73));
                        tdhyjson.put("tdhylxdm","10");
                    }else if("拖拉机和联合收割机".equals(tdhylx)){
                        // 是否用于办理拖拉机和联合收割机登记	发动机号码	底盘号/机架号
                        tdhyjson.put("sfyybltljhlhsgjdj",sjMap.get(74));
                        tdhyjson.put("fdjhm",sjMap.get(75));
                        tdhyjson.put("dphjjh",sjMap.get(76));
                        tdhyjson.put("tdhylxdm","13");
                    }
                }
            }
        }
        return tdhyjson;
    }


    //测试
    public static void main(String[] args) {
        File file =new  File ("C:\\Users/htjs/Desktop/NSR-开票业务-批量导入开票-导入开票模板.xlsx");
        try {
            Map map=null;
            //importExcel_sjmb1(file,map);



        } catch (Exception e) {
            logger.error("--->读取excel失败，{0}", e);
        }


    }


}



